The present invention relates generally to the field of information processing by digital computers and, more particularly, to the processing and presentation of information by electronic spreadsheets.
Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant""s columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of xe2x80x9celectronic spreadsheets.xe2x80x9d Better known simply as xe2x80x9cspreadsheets,xe2x80x9d these software programs provide a computerized replacement for the traditional financial modeling tools: the accountant""s columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what wordprocessors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant""s columnar pad, thus providing a visible calculator for a user. Because this xe2x80x9cpadxe2x80x9d exists dynamically in the computer""s memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, and a letter to each column. To reference a location at column A and row 1 (i.e., the upper-lefthand corner), for example, the user types in xe2x80x9cA1xe2x80x9d. In this manner, the spreadsheet defines an addressable storage location or xe2x80x9ccellxe2x80x9d at each intersection of a row with a column.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant""s pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or xe2x80x9cformulasxe2x80x9d specifying calculations to be performed on the numbers stored in spreadsheet cells. In this fashion, cell references can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-Osborne/McGraw-Hill, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que Corp., 1985. The disclosures of each of the foregoing are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform xe2x80x9cwhat-ifxe2x80x9d scenarios. After a set of computational relationships has been entered into a worksheet, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Thus, electronic spreadsheet systems were invented to solve xe2x80x9cwhat-ifxe2x80x9d problems, that is, changing an input and seeing what happens to an output.
More recent implementations of these systems have added xe2x80x9csolversxe2x80x9d or xe2x80x9coptimizers.xe2x80x9d This adds goal-seeking functionality where a user can reverse the what-if processxe2x80x94deciding what value one wants an output to assume, with the system determining the appropriate input value(s). In a typical implementation, the user can set a target value at one cell, then specify both multiple input variables and multiple constraint cells. The optimizer finds all combinations of input values that achieve the target output without violating the constraints. Typically, a user employs such an optimizer to maximize or minimize an output cell (rather than aiming for a specific target value).
These backsolvers and optimizers are a good first step at improving the what-if process. To date, however, electronic spreadsheet systems have not been particularly adept at the process of actually managing the multitude of what-if scenarios, that is, multiple variations spawn from a single model. Since a given spreadsheet model is routinely created under a set of assumptions (e.g., level of sales, corporate tax rate, and the like), it is desirable to test the extremes of one""s assumptions to ascertain the likely results. Although such xe2x80x9cbest case/worst casexe2x80x9d analyses are commonly required by users, present-day systems have provided little or no tools for creating and managing such a multitude of scenarios. Instead, the user must resort to manually creating separate copies of the underlying model, with the user responsible for tracking any modifications made in the various copies. As this approach is undesirable at best, there is a great need for a better solution.
The present invention comprises an electronic spreadsheet system having a preferred interface and methods for creating and tracking various versions or xe2x80x9cscenariosxe2x80x9d of a data model. The system includes tools for specifying a xe2x80x9ccapture area,xe2x80x9d that is, a specific set of information cells to be tracked and an Identify Scenario tool for automatically determining changes between a captured parent or baseline model and a new scenario. Screen feedback techniques are provided for highlighting xe2x80x9cchanging cellsxe2x80x9d and xe2x80x9cresult cellsxe2x80x9d identified by the system.
In an exemplary method of the present invention, the user first specifies a capture area (such as notebook, page, or block) and a baseline (i.e., the base or standard case which is to serve as a reference). Next, the user proceeds to modify the worksheet, for example, changing the various assumptions (e.g., tax rate) employed to create the model. At the conclusion of the user-specified changes, the system locates any differences in non-formula cells when compared against corresponding ones in the baseline; these cells are xe2x80x9cchanging cells.xe2x80x9d Furthermore, the system determines any formula cells which express a different value as a result of changes in the changing cells. These cells, which may directly or indirectly reference one or more changing cells, are xe2x80x9cresult cells.xe2x80x9d After identifying these changing and result cells, the system may demonstrate them to the user (e.g., employing color highlights or the like).
The user may proceed to create a plurality of such scenarios, by simply repeating the foregoing process; each scenario so added is typically given a descriptive name by the user. For convenience, the system provides the user facilities for creating various groupings of scenarios; like each scenario, each group may be given an easy-to-remember descriptive name by the user. Individual scenarios may be added or deleted from one or more groups as desired. After creating desired scenarios, the user may instruct the system to save these new versions, together with the baseline, on a storage device. In a preferred embodiment, the new versions are stored together with the baseline, so that redundant information between the baseline and a particular version is not duplicated. A preferred method of the present invention for xe2x80x9cversioningxe2x80x9d (i.e., tracking and storing various versions of a base model) employs difference or delta records for recording the various changes which result with each new version.